package inet.db.tennis;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import inet.db.pool.*;
import inet.util.Logger;

public class TennisSetDAO {

	private Logger logger = null;
	private DBPoolX poolX = null;
	public TennisSetDAO(){
		logger = new Logger(this.getClass().getName());
		try{
			poolX = DBPoolX.getInstance(DBPoolXName.SERVICE_BONGDA);
		}catch(Exception exception){
			logger.log(exception.toString()); 
		}
	}
	
	/**
	 * function get a TennisSet by id
	 * @param id
	 * @return
	 */
    public TennisSet getRow(BigDecimal id) {
        Connection conn = null;
        PreparedStatement preStmt = null;
        ResultSet rs = null;
        String strSQL = null;
        TennisSet set = null;
        try {
            conn = poolX.getConnection();
            strSQL =
                "select ID, MATCH_ID, SCORE1, SCORE2, VALUE, STATUS, TIME_SET, TIE_BREAK_SCORE1, TIE_BREAK_SCORE2, GEN_DATE, LAST_UPDATED " +
                "from TENNIS_SET where ID = ? ";
            preStmt = conn.prepareStatement(strSQL);
            preStmt.setBigDecimal(1, id);
            rs = preStmt.executeQuery();
            if (rs.next()) {
            	set = new TennisSet();
            	set.setId(rs.getBigDecimal(1));
            	set.setMatchId(rs.getBigDecimal(2));
            	set.setScore1(rs.getInt(3));
            	set.setScore2(rs.getInt(4));
            	set.setValue(rs.getInt(5));
            	set.setStatus(rs.getInt(6));
            	set.setTimeSet(rs.getInt(7));
            	set.setTieBreakScore1(rs.getInt(8));
            	set.setTieBreakScore2(rs.getInt(9));
            	set.setGenDate(rs.getTimestamp(10));
            	set.setLastUpdated(rs.getTimestamp(11));
            }
        }
        catch (SQLException e) {
            logger.error("getRow: Error executing SQL " + strSQL + ">>>" + e.toString());
        } finally {
            poolX.releaseConnection(conn, preStmt, rs);
        }
        return set;
    }
    
    /**
     * function get last set of match
     * @param matchId
     * @return TennisSet object
     */
    public TennisSet getCurrentSetOfMatch(BigDecimal matchId) {
    	Connection conn = null;
    	PreparedStatement preStmt = null;
    	ResultSet rs = null;
    	String strSQL = null;
    	TennisSet set = null;
    	try {
    		conn = poolX.getConnection();
    		strSQL =
    			"select ID, MATCH_ID, SCORE1, SCORE2, VALUE, STATUS, TIME_SET, TIE_BREAK_SCORE1, TIE_BREAK_SCORE2, GEN_DATE, LAST_UPDATED " +
    			"from TENNIS_SET where MATCH_ID = ? order by ID desc, VALUE desc";
    		preStmt = conn.prepareStatement(strSQL);
    		preStmt.setBigDecimal(1, matchId);
    		rs = preStmt.executeQuery();
    		if (rs.next()) {
    			set = new TennisSet();
    			set.setId(rs.getBigDecimal(1));
    			set.setMatchId(rs.getBigDecimal(2));
    			set.setScore1(rs.getInt(3));
    			set.setScore2(rs.getInt(4));
    			set.setValue(rs.getInt(5));
    			set.setStatus(rs.getInt(6));
    			set.setTimeSet(rs.getInt(7));
    			set.setTieBreakScore1(rs.getInt(8));
    			set.setTieBreakScore2(rs.getInt(9));
    			set.setGenDate(rs.getTimestamp(10));
    			set.setLastUpdated(rs.getTimestamp(11));
    		}
    	}
    	catch (SQLException e) {
    		logger.error("getRow: Error executing SQL " + strSQL + ">>>" + e.toString());
    	} finally {
    		poolX.releaseConnection(conn, preStmt, rs);
    	}
    	return set;
    }
    
    /**
     * fucntion get all TennisSet
     * @return Vector 
     * TennisSet (Full)
     */
    public Vector findAll() {
    	Connection conn = null;
    	PreparedStatement preStmt = null;
    	ResultSet rs = null;
    	String strSQL = null;
    	TennisSet set = null;
    	Vector vSet = null;
    	
    	try {
    		conn = poolX.getConnection();
    		strSQL =
    			"select ID, MATCH_ID, SCORE1, SCORE2, VALUE, STATUS, TIME_SET, TIE_BREAK_SCORE1, TIE_BREAK_SCORE2, GEN_DATE, LAST_UPDATED " +
                "from TENNIS_SET order by ID desc, GEN_DATE desc ";
    		preStmt = conn.prepareStatement(strSQL);
    		rs = preStmt.executeQuery();
    		vSet = new Vector();
    		while (rs.next()) {
    			set = new TennisSet();
            	set.setId(rs.getBigDecimal(1));
            	set.setMatchId(rs.getBigDecimal(2));
            	set.setScore1(rs.getInt(3));
            	set.setScore2(rs.getInt(4));
            	set.setValue(rs.getInt(5));
            	set.setStatus(rs.getInt(6));
            	set.setTimeSet(rs.getInt(7));
            	set.setTieBreakScore1(rs.getInt(8));
            	set.setTieBreakScore2(rs.getInt(9));
            	set.setGenDate(rs.getTimestamp(10));
            	set.setLastUpdated(rs.getTimestamp(11));
    			
            	vSet.addElement(set);
    		}
    	}
    	catch (SQLException e) {
    		logger.error("findAll: Error executing SQL " + strSQL + ">>>" + e.toString());
    	} finally {
    		poolX.releaseConnection(conn, preStmt, rs);
    	}
    	return vSet;
    }
	
    /**
     * function insert a Tennis SET
     * @param matchId
     * @param set
     * @param status
     * @return true if insert success else return false.
     */
	public BigDecimal insertRow(BigDecimal matchId, int set, int status){
		Connection conn = null;
        PreparedStatement preStmt = null;
        ResultSet rs = null;
        BigDecimal result = BigDecimal.ZERO;
        String strSQL = null;
        BigDecimal id = BigDecimal.ZERO;
        try {
            conn = poolX.getConnection();
            
            strSQL = "SELECT TENNIS_SET_SEQ.nextval FROM dual";
			preStmt = conn.prepareStatement(strSQL);
			rs = preStmt.executeQuery();
			if (rs.next()) {
				id = rs.getBigDecimal(1);
			}
			preStmt.close();
            
        	strSQL =" insert into TENNIS_SET( ID, MATCH_ID, VALUE, STATUS ) " +
    				" values(?, ?, ?, ?) ";
	        preStmt = conn.prepareStatement(strSQL);
	        preStmt.setBigDecimal(1, id);
	        preStmt.setBigDecimal(2, matchId);
	        preStmt.setInt(3, set);
	        preStmt.setInt(4, status);
	        
	        if (preStmt.executeUpdate()>=1) {
	        	result = id;
	        }
        } catch (Exception ex) {
        	logger.error("insertRow: Error executing SQL " + strSQL + ">>>" + ex.toString());
        } finally {
        	poolX.releaseConnection(conn, preStmt, rs);
       }
       return result;
	}
	
	/**
	 * function update Score1 and score2 of SET.
	 * @param id
	 * @param score1
	 * @param score2
	 * @param status
	 * @return true if update success else return false.
	 */
	public boolean updateScore(BigDecimal id, int score1, int score2, int status){
		Connection conn = null;
        PreparedStatement preStmt = null;
        boolean result = false;
        String strSQL = null;
        try {
        	conn = poolX.getConnection();
        	strSQL =" update TENNIS_SET set SCORE1 = ?, SCORE2 = ?, STATUS = ?, LAST_UPDATED = sysdate " +
        			" where ID = ?";
        	preStmt = conn.prepareStatement(strSQL);
        	preStmt.setInt(1, score1);
        	preStmt.setInt(2, score2);
        	preStmt.setInt(3, status);
        	preStmt.setBigDecimal(4, id);
        	
        	if(preStmt.executeUpdate() > 0){
        		result = true;
        	}
        } catch (Exception ex) {
        	logger.error("updateScore: Error executing SQL " + strSQL + ">>>" + ex.toString());
        } finally {
        	poolX.releaseConnection(conn, preStmt);
       }
        return result;
	}
	
	/**
	 * function update score of Tennis set.
	 * @param id
	 * @param score1
	 * @param score2
	 * @param tieBreakScore1
	 * @param tieBreakScore2
	 * @param status
	 * @return true if update success else return false.
	 */
	public boolean updateScore(BigDecimal id, int score1, int score2, int tieBreakScore1, int tieBreakScore2, int status){
		Connection conn = null;
		PreparedStatement preStmt = null;
		boolean result = false;
		String strSQL = null;
		try {
			conn = poolX.getConnection();
			strSQL =" update TENNIS_SET set SCORE1 = ?, SCORE2 = ?, TIE_BREAK_SCORE1 = ?, TIE_BREAK_SCORE2 = ?, STATUS = ?, LAST_UPDATED = sysdate " +
			" where ID = ?";
			preStmt = conn.prepareStatement(strSQL);
			preStmt.setInt(1, score1);
			preStmt.setInt(2, score2);
			preStmt.setInt(3, tieBreakScore1);
			preStmt.setInt(4, tieBreakScore2);
			preStmt.setInt(5, status);
			preStmt.setBigDecimal(6, id);
			
			if(preStmt.executeUpdate() > 0){
				result = true;
			}
		} catch (Exception ex) {
			logger.error("updateScore: Error executing SQL " + strSQL + ">>>" + ex.toString());
		} finally {
			poolX.releaseConnection(conn, preStmt);
		}
		return result;
	}
	
	/**
	 * function update tie break score of set.
	 * @param id
	 * @param tieBreakScore1
	 * @param tieBreakScore2
	 * @return true if update success else return false.
	 */
	public boolean updateTieBreakScore(BigDecimal id, int tieBreakScore1, int tieBreakScore2){
		Connection conn = null;
		PreparedStatement preStmt = null;
		boolean result = false;
		String strSQL = null;
		try {
			conn = poolX.getConnection();
			strSQL =" update TENNIS_SET set TIE_BREAK_SCORE1 = ?, TIE_BREAK_SCORE2 = ?, LAST_UPDATED = sysdate " +
			" where ID = ?";
			preStmt = conn.prepareStatement(strSQL);
			preStmt.setInt(1, tieBreakScore1);
			preStmt.setInt(2, tieBreakScore2);
			preStmt.setBigDecimal(3, id);
			
			if(preStmt.executeUpdate() > 0){
				result = true;
			}
		} catch (Exception ex) {
			logger.error("updateTieBreakScore: Error executing SQL " + strSQL + ">>>" + ex.toString());
		} finally {
			poolX.releaseConnection(conn, preStmt);
		}
		return result;
	}
	
	/**
	 * function update tie break score, score, and status of set.
	 * @param id
	 * @param score1
	 * @param score2
	 * @param tieBreakScore1
	 * @param tieBreakScore2
	 * @param status
	 * @return true if update success else return false.
	 */
	public boolean updateTieBreakScore(BigDecimal id, int score1, int score2, int tieBreakScore1, int tieBreakScore2, int status){
		Connection conn = null;
		PreparedStatement preStmt = null;
		boolean result = false;
		String strSQL = null;
		try {
			conn = poolX.getConnection();
			strSQL =" update TENNIS_SET set SCORE1 = ?, SCORE2 = ?, TIE_BREAK_SCORE1 = ?, TIE_BREAK_SCORE2 = ?, STATUS = ?, LAST_UPDATED = sysdate " +
			" where ID = ?";
			preStmt = conn.prepareStatement(strSQL);
			preStmt.setInt(1, score1);
			preStmt.setInt(2, score2);
			preStmt.setInt(3, tieBreakScore1);
			preStmt.setInt(4, tieBreakScore2);
			preStmt.setInt(5, status);
			preStmt.setBigDecimal(6, id);
			
			if(preStmt.executeUpdate() > 0){
				result = true;
			}
		} catch (Exception ex) {
			logger.error("updateTieBreakScore: Error executing SQL " + strSQL + ">>>" + ex.toString());
		} finally {
			poolX.releaseConnection(conn, preStmt);
		}
		return result;
	}
	
	/**
	 * function update time set of Tennis set
	 * @param id
	 * @param timeSet
	 * @return true if update success else return false.
	 */
	public boolean updateTimeSet(BigDecimal id, int timeSet){
		Connection conn = null;
		PreparedStatement preStmt = null;
		boolean result = false;
		String strSQL = null;
		try {
			conn = poolX.getConnection();
			strSQL =" update TENNIS_SET set TIME_SET = ?, LAST_UPDATED = sysdate " +
			" where ID = ?";
			preStmt = conn.prepareStatement(strSQL);
			preStmt.setInt(1, timeSet);
			preStmt.setBigDecimal(2, id);
			
			if(preStmt.executeUpdate() > 0){
				result = true;
			}
		} catch (Exception ex) {
			logger.error("updateTimeSet: Error executing SQL " + strSQL + ">>>" + ex.toString());
		} finally {
			poolX.releaseConnection(conn, preStmt);
		}
		return result;
	}
	
	/**
	 * function update status of tennis set
	 * @param id
	 * @param status
	 * @return true if update success else return false.
	 */
	public boolean updateStatus(BigDecimal id, int status){
		Connection conn = null;
		PreparedStatement preStmt = null;
		boolean result = false;
		String strSQL = null;
		try {
			conn = poolX.getConnection();
			strSQL =" update TENNIS_SET set STATUS = ?, LAST_UPDATED = sysdate " +
			" where ID = ?";
			preStmt = conn.prepareStatement(strSQL);
			preStmt.setInt(1, status);
			preStmt.setBigDecimal(2, id);
			
			if(preStmt.executeUpdate() > 0){
				result = true;
			}
		} catch (Exception ex) {
			logger.error("updateStatus: Error executing SQL " + strSQL + ">>>" + ex.toString());
		} finally {
			poolX.releaseConnection(conn, preStmt);
		}
		return result;
	}

	/**
	 * function delete a Tennis Set
	 * @param id
	 * @return true if update success else return false.
	 */
	public boolean deleteRow(BigDecimal id){
		Connection conn = null;
		PreparedStatement preStmt = null;
		boolean result = false;
		String strSQL = null;
		try {
			conn = poolX.getConnection();
			strSQL =" delete from TENNIS_SET where ID = ? ";
			preStmt = conn.prepareStatement(strSQL);
			preStmt.setBigDecimal(1, id);
			if(preStmt.executeUpdate() > 0){
				result = true;
			}
		} catch (Exception ex) {
			logger.error("deleteRow: Error executing SQL " + strSQL + ">>>" + ex.toString());
		} finally {
			poolX.releaseConnection(conn, preStmt);
		}
		return result;
	}
	
	/**
	 * 
	 * @param matchId
	 * @param set
	 * @return
	 */
	public boolean deleteRow(BigDecimal matchId, int set){
		Connection conn = null;
		PreparedStatement preStmt = null;
		boolean result = false;
		String strSQL = null;
		try {
			conn = poolX.getConnection();
			strSQL =" delete from TENNIS_SET where MATCH_ID = ? and VALUE = ? ";
			preStmt = conn.prepareStatement(strSQL);
			preStmt.setBigDecimal(1, matchId);
			preStmt.setInt(2, set);
			if(preStmt.executeUpdate() > 0){
				result = true;
			}
		} catch (Exception ex) {
			logger.error("deleteRow: Error executing SQL " + strSQL + ">>>" + ex.toString());
		} finally {
			poolX.releaseConnection(conn, preStmt);
		}
		return result;
	}
}
